var express = require('express');
let mysqlExec = require('../db/dbMySql.js');
let { Msg, curPath } = require('../tools/msg.js');
var router = express.Router();

//设计列表页用到的所有路由

//列表页面中的城市接口：
router.get('/citys', async(req, res) => {

    let sql = `select * from e_area`;
    let [e, d] = await mysqlExec(sql);
    if (e) {
        res.send(Msg(200, '列表页中的城市查询成功', d));
    } else {
        res.send(Msg(500, '列表页中的城市查询失败'));
    }

});


//列表页面中的年级接口：
router.get('/grade', async(req, res) => {

    let sql = `select * from e_grade`;
    let [e, d] = await mysqlExec(sql);
    if (e) {
        res.send(Msg(200, '列表页中的年级查询成功', d));
    } else {
        res.send(Msg(500, '列表页中的年级查询失败'));
    }

});

//列表页面中的学科接口：
router.get('/subject', async(req, res) => {

    let sql = `select * from e_subject`;
    let [e, d] = await mysqlExec(sql);
    if (e) {
        res.send(Msg(200, '列表页中的学科查询成功', d));
    } else {
        res.send(Msg(500, '列表页中的学科查询失败'));
    }

});

//列表页面中的类别接口：
router.get('/category', async(req, res) => {

    let sql = `select * from e_category`;
    let [e, d] = await mysqlExec(sql);
    if (e) {
        res.send(Msg(200, '列表页中的类别查询成功', d));
    } else {
        res.send(Msg(500, '列表页中的类别查询失败'));
    }

});


//列表页面中的课程接口：
router.get('/courselist', async(req, res) => {

    //ctype:课程类别
    //limits:每页要显示的条数
    //pages:第几页 
    //city:城市
    //grade:年级
    //subject:学科
    //category:类别
    let { ctype = 1, limits = 3, pages = 1, city = '', grade = '', subject = '', category = '' } = req.query;
    //计算游标位置(m)=(第几页-1)*每页要显示的条数
    let starts = (pages - 1) * limits;

    let wheres = '';
    if (city != '') {
        wheres = ` and area_name='${city}' `;
    }

    if (grade != '') {
        wheres += ` and grade_name='${grade}' `
    }

    if (subject != '') {
        wheres += ` and subject_name='${subject}' `;
    }

    if (category != '') {
        wheres += ` and category_name='${category}' `;
    }

    //根据查询条件查询课程数据：
    let sql = `select cid,title,price,concat('${curPath}course/',image_src) as image_src,type_name,area_name,subject_name from e_course where type=${ctype} ${wheres} limit ${starts},${limits}`;
    let [err, datas] = await mysqlExec(sql);

    // 查询总的记录个数
    sql = `select count(cid) as m from e_course where type=${ctype} ${wheres}`;
    let [e, d] = await mysqlExec(sql);
    //将总的记录个数放在datas数组的头部
    if (e) {
        datas.unshift(d[0].m);
    } else {
        datas.unshift(0);
    }

    if (err) {
        res.send(Msg(200, '列表页中的课程查询成功', datas));
    } else {
        res.send(Msg(500, '列表页中的课程查询失败'));
    }


});


//搜索课程的接口：
router.get('/search', async(req, res) => {
    //接参(用户输入的关键词)
    let { keyword = '', limits = 3, pages = 1 } = req.query;
    if (keyword == '') {
        res.send(Msg(500, '查询关键词不能为空'));
        return;
    }

    //根据用户输入的关键词查询课程总数：
    let sql = `select count(id)as m from e_course where title like '%${keyword}%'`;
    let [e, d] = await mysqlExec(sql);
     
//    if(e){
//        res.send(d,999);
//    }
    //计算游标位置：
    let start = (pages - 1) * limits;

    //根据用户输入的关键词查询课程数据：
    sql = `select cid,title,price,concat('${curPath}course/',image_src) as image_src,area_name,subject_name from e_course where title like '%${keyword}%' limit ${start},${limits}`;

    let [err, data] = await mysqlExec(sql);
    if (err) {
        data.unshift(d[0].m);
        res.send(Msg(200, '课程查询成功', data));
    } else {
        res.send(Msg(500, '课程查询失败'));
    }

});

module.exports = router;